/*
    Project Milestone 3
    by Lorenzo Miori [Lorenzo.Miori@stud-inf.unibz.it,
    Matteo Angriman [Matteo.Angriman@stud-inf.unibz.it],
    Nick Preda [Nicolae.Preda@stud-inf.unibz.it]
*/

package DB;

import static DB.GenericDBInterface.BOOLEAN;
import static DB.GenericDBInterface.DATE;
import static DB.GenericDBInterface.INT;
import static DB.GenericDBInterface.STRING;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import sun.reflect.generics.reflectiveObjects.NotImplementedException;

/**
 * This actually implements the basic connector to the database -> implements
 * GenericDBInterface
 *
 * @author Lorenzo
 */
public abstract class GenericTableInterface implements GenericDBInterface {

    /* We define a connection that will be available for SQL operations */
    private Connection databaseConnection;
    /* We must know data types when handling data */
    private int[] dataTypes;
    private String[] dataOrdering;
    /* We must know the name of the table */
    private String tableName;
    /* We must know the ID_FIELD of the primary key in order to do most operations */
    private String IDString;

    public GenericTableInterface(Connection dbConnection, int[] dataTypes, String IDString, String tableName, String[] ordering) {
        databaseConnection = dbConnection;
        this.dataTypes = dataTypes;
        this.IDString = IDString;
        this.tableName = tableName;
        this.dataOrdering = ordering;
    }

    public static String joinComma(List<String> list) {
        String res = "";
        for (Iterator<String> iterator = list.iterator(); iterator.hasNext();) {
            res += iterator.next() + (iterator.hasNext() ? "," : "");
        }
        return res;
    }

    public static String joinEquals(List<String> list) {
        String res = "";
        for (Iterator<String> iterator = list.iterator(); iterator.hasNext();) {
            res += iterator.next() + "=?" + (iterator.hasNext() ? "," : "");
        }
        return res;
    }

    public static String joinChar(String d, int count) {
        String o = "(";
        for (int i = 0; i < count; i++) {
            o += d;
            o += ",";
        }
        // get rid of the bad comma...
        if (o.endsWith(",")) {
            o = o.substring(0, o.length() - 1);
        }
        o += ")";
        return o;
    }

    /**
     * Returns an array with all the tuples present in a table, defined by the
     * implementing class
     *
     * @return
     */
    @Override
    public ArrayList<HashMap<String, String>> getTableData() {

        ArrayList<HashMap<String, String>> output = new ArrayList<HashMap<String, String>>();
        Statement st = null;
        ResultSet rs = null;
        ResultSetMetaData rsmd;
        try {
            st = databaseConnection.createStatement();
            /* SELECT */
            rs = st.executeQuery("select * from " + tableName);
            while (rs.next()) {
                HashMap<String, String> newEntry = new HashMap<String, String>();
                rsmd = rs.getMetaData();
                for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                    newEntry.put(rsmd.getColumnName(i), rs.getString(i));
                }
                output.add(newEntry);
            }

        } catch (SQLException ex) {
            System.err.println(ex.getMessage());
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (st != null) {
                    st.close();
                }

            } catch (SQLException ex) {
                System.err.println(ex.getMessage());
            }
        }
        return output;
    }

    @Override
    public HashMap<String, String> getRowByID(int ID) {
        for (HashMap<String, String> entry : getTableData()) {
            if (Integer.parseInt(entry.get(IDString)) == ID) {
                return entry;
            }
        }
        return null;
    }

    @Override
    public boolean setTableData(HashMap<String, String> data) {
        /* ID of the tuple that's going to be inserted/modified */
        int newID = Integer.parseInt(data.get(IDString));
        boolean newTuple = true;
        boolean success = false;
        for (HashMap<String, String> anEntry : getTableData()) {
            if (Integer.parseInt(anEntry.get(IDString)) == newID) {
                newTuple = false;
            }
        }

        PreparedStatement statement = null;
        /* Prepare the statement according to what has to be done... */
        try {
            if (newTuple) {

                System.out.println("inserting tuple...");

                statement = databaseConnection.prepareStatement(
                        "INSERT INTO " + tableName
                        + " (" + joinComma(Arrays.asList(dataOrdering))
                        + ") values"
                        + " " + joinChar("?", dataOrdering.length) + ";");

            } else {
                System.out.println("updating tuple...");
                /*
                 * UPDATE table_name
                 SET column1=value, column2=value2,...
                 WHERE some_column=some_value
                 */

                statement = databaseConnection.prepareStatement(
                        "UPDATE " + tableName
                        + " SET "
                        + joinEquals(Arrays.asList(dataOrdering))
                        + " WHERE " + IDString + "=" + newID);
            }
        } catch (SQLException e) {
            System.err.println(e.getMessage());
        }

        if (statement == null) {
            return false;
        }
        /* let's insert values in the actual query */
        try {
            int i = 1;
            for (String key : dataOrdering) {
                System.out.println(key + " " + dataTypes[i - 1]);
                switch (dataTypes[i - 1]) {
                    case INT:
                        statement.setInt(i, Integer.parseInt(data.get(key)));
                        break;
                    case STRING:
                        statement.setString(i, data.get(key));
                        break;
                    case BOOLEAN:
                        statement.setBoolean(i,
                                (data.get(key).charAt(0) == 't') ? true : false);
                        System.out.println(((data.get(key).charAt(0) == 't') ? true : false));
                    case DATE:
// Example string should be --> String target = "2012-12-14";
                        String[] components = data.get(key).split("-");
//                            DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
//                            Date result = null;
                        // FIXME deprecated method...
                        java.sql.Date newDate = new java.sql.Date(Integer.parseInt(components[0]),
                                Integer.parseInt(components[1]),
                                Integer.parseInt(components[2]));
                        statement.setDate(i, newDate);
                    default:
                        System.out.println(dataTypes[i - 1]);
                        throw new NotImplementedException();

                }
                i++;
            }

            System.out.println(statement);
            statement.executeUpdate();
            success = true;

        } catch (SQLException ex) {
            System.err.println(ex.getMessage());
        } finally {
            try {
                statement.close();
                success = true;
            } catch (SQLException ex) {
                System.err.println(ex.getMessage());
            }
        }

        return success;
    }

    /**
     * Removes a tuple from a table TODO : allow DROP CASCADE
     *
     * @param uniqueID
     * @return
     */
    @Override
    public boolean removeTupleFromTable(int uniqueID) {
        String statement = "DELETE FROM " + tableName;
        statement += " WHERE " + IDString + "=" + uniqueID;
        Statement st = null;
        boolean deleted = false;
        try {
            st = databaseConnection.createStatement();
            st.executeUpdate(statement);
            deleted = true;
        } catch (java.sql.SQLException e) {
            deleted = false;
            System.out.println("Caught SQLException " + e.getErrorCode()
                    + "/" + e.getSQLState() + " " + e.getMessage());
        } finally {
            try {
                if (st != null) {
                    st.close();
                }
            } catch (SQLException ex) {
                System.err.println(ex.getMessage());
            }
        }
        return deleted;
    }

    @Override
    public int getUniqueID() {
        int ID = 0; // in case of no records, just start from zero
        for (HashMap<String, String> anEntry : getTableData()) {
            if (Integer.parseInt(anEntry.get(IDString)) > ID) /* The policy is to add 1 to the last ID */ {
                ID = Integer.parseInt(anEntry.get(IDString));
            }
        }
        return ID + 1;
    }
}
